package com.blackcat.recode.dao;

import com.blackcat.recode.entity.Project;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p> 项目信息表 Mapper 接口
 * @author blackcat 2024-09-10
 */
public interface ProjectMapper extends BaseMapper<Project> {

    @Select("SELECT  p.project_name, s.service_name, b.branch_name, ur.version_no,  DATE(ur.create_time) AS modify_time\n" +
            "FROM  project p\n" +
            "JOIN  service s ON p.id = s.project_id\n" +
            "JOIN  branch b ON s.id = b.service_id\n" +
            "JOIN  update_recode ur ON b.id = ur.branch_id\n" +
            "JOIN (\n" +
            "    SELECT branch_id, MAX(version_no) AS max_version\n" +
            "    FROM update_recode\n" +
            "    GROUP BY branch_id\n" +
            ") max_versions ON ur.branch_id = max_versions.branch_id AND ur.version_no = max_versions.max_version\n" +
            "ORDER BY p.project_name, s.service_name, b.branch_name")
    List<Map<String,String>> getAllServiceList();

    @Select("SELECT c.customer_name, s.service_name, b.branch_name, ur.version_no, DATE(dr.create_time) AS deploy_time\n" +
            "FROM customer c\n" +
            "JOIN deploy_recode dr ON c.id = dr.customer_id\n" +
            "JOIN update_recode ur ON dr.update_id = ur.id\n" +
            "JOIN branch b ON ur.branch_id = b.id\n" +
            "JOIN service s ON b.service_id = s.id\n" +
            "JOIN (\n" +
            "    SELECT dr.customer_id, s.id AS service_id, MAX(DATE(dr.create_time)) AS latest_deploy_time\n" +
            "    FROM deploy_recode dr\n" +
            "    JOIN update_recode ur ON dr.update_id = ur.id\n" +
            "    JOIN branch b ON ur.branch_id = b.id\n" +
            "    JOIN service s ON b.service_id = s.id\n" +
            "    GROUP BY dr.customer_id, s.id\n" +
            ") latest_deploys \n" +
            "ON c.id = latest_deploys.customer_id AND s.id = latest_deploys.service_id AND DATE(dr.create_time) = latest_deploys.latest_deploy_time\n" +
            "ORDER BY c.customer_name, s.service_name")
    List<Map<String,String>> getAllCustomerList();
}
